package db;

import dao.DBAccess;
import dao.DBException;

import util.Config;
import util.Debug;
import util.FileAccess;

import java.io.File;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import javax.swing.JOptionPane;

/**
 * Initialize the current database (interactive).
 * Can delete, create and populate the necessary tables.
 * @author Jolle
 * @version 1.0.0
 */
public class DBCheck {

	public boolean initDB() throws DBException {
		/* set flag to suppress messages */
		boolean noQuestions = !Config.SHOW_DB_CONFIG_DIALOG;
		/* create db access object and connect to database */
		DBAccess dba = DBAccess.getInstance();
		/* get database name */
		String dbName = dba.getDbName();
		Debug.print("connected to database: " + dbName);
		
		/* list all databases/schemas */
		String[] dbs = dba.getDataBasesList();
		for (String db : dbs) {
			Debug.print("database/schema: " + db);
		}

		if (!noQuestions) {
			/* list all tables */
			if (!listAllTables(noQuestions)) {
				closeConnection(dba);
				return false;
			}
		}
		
		/* check table countrymodel */
		String tableName = "countrymodel";
		if (checkCreateTable(tableName, noQuestions)) {
			String countriesFilePath = Config.APP_DIR + "database"+File.separator+"countries.csv";
			populateCountriesTable(countriesFilePath);
		}
		
		if (!noQuestions) {
			/* list all tables */
			if (!listAllTables(noQuestions)) {
				closeConnection(dba);
				return false;
			}
		}
		
		if (!noQuestions) {
			/* show contents of table countrymodel */
			//showTableContent(tableName);
			showTableContentWithFieldNames(tableName);
		}
		
		/* check table citymodel */
		tableName = "citymodel";
		if (checkCreateTable(tableName, noQuestions)) {
			String citiesFilePath = Config.APP_DIR + "database"+File.separator+"stations.csv";
			populateCitiesTable(citiesFilePath);
		}
		
		if (!noQuestions) {
			/* list all tables */
			if (!listAllTables(noQuestions)) {
				closeConnection(dba);
				return false;
			}
		}
		
		if (!noQuestions) {
			/* show contents of table citymodel */
			showTableContent(tableName);
			//showTableContentWithFieldNames(tableName);
		}
		
		/* check table weathermodel */
		tableName = "weathermodel";
		checkCreateTable(tableName, noQuestions);
		
		/* list all tables */
		if (!listAllTables(noQuestions)) {
			closeConnection(dba);
			return false;
		}
		
		if (!noQuestions) {
			/* show contents of table weathermodel */
			//showTableContent(tableName);
			showTableContentWithFieldNames(tableName);
		}
		
		/* close connection */
		closeConnection(dba);
		return true;
	}
	
	/**
	 * Check whether the given table should be created (interactive).
	 * @param tableName
	 * @return true if table has been created, otherwise false
	 * @throws DBException
	 */
	private boolean checkCreateTable(String tableName, boolean noQuestions) throws DBException {
		DBAccess dba = DBAccess.getInstance();
		String dbName = dba.getDbName();
		int answer;
		if (dba.tableExists(tableName)) {
			if (noQuestions) {
				return false;	// Data already exist
			}
			else {
				String message = "Table '" + tableName + "' exists in database '" + 
						dbName + ".'\n\nShould it be deleted?";
				answer = JOptionPane.showConfirmDialog(null, message,
						"Confirm delete table", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
				if (answer == JOptionPane.YES_OPTION) {
					Debug.print("deleting table '" + tableName + "' ...");
					dba.execute("DROP TABLE " + tableName);
				} else {
					return false;	// Data already exist
				}
			}
		}
		if (noQuestions) {
			answer = JOptionPane.YES_OPTION;
		}
		else {
			String message = "Table '" + tableName + "' doesn't exist in database '" + 
					dbName + "'.\n\nShould it be created?";
			answer = JOptionPane.showConfirmDialog(null, message,
					"Confirm create table", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
		}
		if (answer == JOptionPane.YES_OPTION) {
			Debug.print("creating table '" + tableName + "' ...");
			dba.createTable(tableName);
			return true;
		}
		else {
			return false;
		}
	}
	/**
	 * Populate table "countrymodel".
	 * @param fullPath to the data file (csv)
	 * @throws DBException
	 */
	private void populateCountriesTable(String fullPath) throws DBException {
		DBAccess dba = DBAccess.getInstance();
		try {
			Debug.print("populating table 'countrymodel' ...");
			FileAccess fileAccess = new FileAccess();
			while (true) {
				String line = fileAccess.readLine(fullPath);
				if (line == null) {
					break;
				}
				if (!line.contains(";")) {
					continue;
				}
				String[] parts = line.split(";");
				String cnid = parts[0].trim();
				String name = parts[1].trim().replaceAll("'", "''");
				if (name.endsWith(",")) {
					name = name.substring(0, name.length() - 1);
				}
				dba.execute("INSERT INTO countrymodel (country_id, name) VALUES " +
						"('" + cnid + "', '" + name + "')");
			}
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Error while populating table countrymodel", e);
		}
	}
	/**
	 * Populate table "citymodel".
	 * @param fullPath to the data file (csv)
	 * @throws DBException
	 */
	private void populateCitiesTable(String fullPath) throws DBException {
		DBAccess dba = DBAccess.getInstance();
		try {
			Debug.print("populating table 'citymodel' ...");
			FileAccess fileAccess = new FileAccess();
			while (true) {
				String line = fileAccess.readLine(fullPath);
				if (line == null) {
					break;
				}
				if (!line.contains(";")) {
					continue;
				}
				String[] parts = line.split(";");
				String ctid = parts[0].trim();
				String ctname = parts[1].trim().replaceAll("'", "''");
				String cnname = parts[2].trim().replaceAll("'", "''");
				String cnid = parts[3].trim();
				dba.execute("INSERT INTO citymodel (country_id, city_id, name, country_name, lat, lon)" +
						" VALUES " +
						"('" + cnid + "', '" + ctid + "', '" + ctname + "', '" + cnname + "', 0, 0)");
			}
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Error while populating table citymodel", e);
		}
	}
	/**
	 * List all table names of the current database.
	 * @throws DBException
	 */
	private boolean listAllTables(boolean noQuestions) throws DBException {
		if (!noQuestions) {
			String message = "List all tables of current database?";
			int answer = JOptionPane.showConfirmDialog(null, message, "Confirm list tables", 
					JOptionPane.YES_NO_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE);
			if (answer == JOptionPane.CANCEL_OPTION) {
				return false;
			}
			if (answer != JOptionPane.YES_OPTION) {
				return true;
			}
		}
		Debug.print("listing tables ...");
		DBAccess dba = DBAccess.getInstance();
		try {
			String[] tables = dba.getTablesList();
			for (String table : tables) {
				Debug.print("Table: " + table);
			}
		} catch (Exception e) {
			throw new DBException("Error while listing tables", e);
		}
		return true;
	}
	/**
	 * Show all data of the given table.
	 * @param tableName
	 * @throws DBException
	 */
	private void showTableContent(String tableName) throws DBException {
		DBAccess dba = DBAccess.getInstance();
		if (!dba.tableExists(tableName)) {
			return;
		}
		String message = "Show content of table '" + tableName + "'?";
		int answer = JOptionPane.showConfirmDialog(null, message,
				"Confirm show content", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
		if (answer != JOptionPane.YES_OPTION) {
			return;
		}
		try {
			Debug.print("content of table '" + tableName + "'");
			ResultSet rs = dba.query("SELECT * FROM " + tableName);
			int colCount = rs.getMetaData().getColumnCount();
			Debug.print("columns: " + colCount);
			int rowCount = 0;
			while (rs.next()) {
				StringBuilder sb = new StringBuilder();
				for (int i = 1; i <= colCount; i++) {
					sb.append(rs.getString(i));
					sb.append(" ");
				}
				Debug.print(sb.toString());
				rowCount++;
				if (rowCount % 1500 == 0) {
					if (JOptionPane.showConfirmDialog(null, "Continue?",
							"Confirm show content", JOptionPane.YES_NO_OPTION, 
							JOptionPane.QUESTION_MESSAGE) != JOptionPane.YES_OPTION) {
						break;
					}
				}
			}
			rs.close();
			dba.closeStatement();
		} catch (Exception e) {
			throw new DBException("Error while listing content of table " + tableName, e);
		}
	}
	
	private void showTableContentWithFieldNames(String tableName) throws DBException {
		DBAccess dba = DBAccess.getInstance();
		if (!dba.tableExists(tableName)) {
			return;
		}
		String message = "Show content of table '" + tableName + "'?";
		int answer = JOptionPane.showConfirmDialog(null, message,
				"Confirm show content", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
		if (answer != JOptionPane.YES_OPTION) {
			return;
		}
		ArrayList<HashMap<String, Object>> list = dba.getResultAsList("SELECT * FROM " + tableName);
		for (int rowCount = 0, size = list.size(); rowCount < size; ++rowCount) {
			HashMap<String, Object> row = list.get(rowCount);
			StringBuilder sb = new StringBuilder();
			for (String fieldName : row.keySet()) {
				sb.append(fieldName);
				sb.append(": ");
				sb.append(row.get(fieldName));
				sb.append("  ");
			}
			Debug.print(sb.toString());
			if ((rowCount + 1) % 1500 == 0) {
				if (JOptionPane.showConfirmDialog(null, "Continue?",
						"Confirm show content", JOptionPane.YES_NO_OPTION, 
						JOptionPane.QUESTION_MESSAGE) != JOptionPane.YES_OPTION) {
					break;
				}
			}
		}
	}
	
	private void closeConnection(DBAccess dba) throws DBException {
		try {
			dba.closeConnection();
		} catch (Exception e) {
			throw new DBException("Error while closing connection", e);
		}
	}
}
